﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using ANZ_AIM_DAL;
using System.Globalization;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;


namespace ANZ_AIM_BLL
{
    public class CandidateBLL
    {
        private const string TABLE_NAME = "Candidates";
        private string ConnectionString;
        private Dictionary<string, string> ObjectPropDict;

        public CandidateBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public void Save(Candidate candidate)
        {
            try
            {
                if (candidate == null)
                {
                    var dict = MiscHelper.Clone(this.ObjectPropDict);
                    OleDbParameter[] parameters;
                    string command = string.Empty;
                    command = CommandHelper.BuildInsertCommand(candidate, dict, out parameters);
                    int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
                else
                {
                    var dict = MiscHelper.Clone(this.ObjectPropDict);
                    OleDbParameter[] parameters;
                    string command = string.Empty;
                    var conditionDict = new Dictionary<string, string>
                    {
                        {"CVCode", "CVCode"}
                    };
                    command = CommandHelper.BuildUpdateCommand(candidate, dict, conditionDict, out parameters);
                    int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
        }
        public bool Save(List<Candidate> candidateList)
        {
            bool flag = true;
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                foreach (Candidate can in candidateList)
                {
                    command = CommandHelper.BuildInsertCommand(can, dict, out parameters);
                    flag = OleDbHelper.ExecuteNonQueryWithRollback(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
            return flag;
        }
        public void Delete(string batchFileID)
        {
            try
            {
                var dict = new Dictionary<string, string>
                    {
                        {"BatchFileID", "BatchFileID"}
                    };
                Candidate candidate = new Candidate() { BatchFileID = batchFileID };
                OleDbParameter[] parameters;
                string command = string.Empty;
                command = CommandHelper.BuildDeleteCommand(candidate, dict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw; }
        }
        public void UpdateBatchFileID(List<Candidate> candidateList)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                dict.Remove("CVCode");

                OleDbParameter[] parameters;
                string command = string.Empty;
                foreach (Candidate can in candidateList)
                {

                    var conditionDict = new Dictionary<string, string>
                    {
                        {"CVCode", "CVCode"}
                    };
                    command = CommandHelper.BuildUpdateCommand(can, dict, conditionDict, out parameters);
                    int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw; }
        }
        public bool CheckCandidateExist(string sCVCode, string IDNumber)
        {
            try
            {
                bool flag = false;
                string command = string.Format("SELECT ID FROM [{0}] WHERE CVCode=@CVCode OR IDNumber=@IDNumber", TABLE_NAME);
                OleDbParameter[] parameters = new OleDbParameter[2];
                parameters[0] = new OleDbParameter("@CVCode", sCVCode);
                parameters[1] = new OleDbParameter("@IDNumber", IDNumber);
                OleDbDataReader reader = OleDbHelper.ExecuteReader(this.ConnectionString, CommandType.Text, command, parameters);
                if (reader.HasRows)
                {
                    flag = true;
                }
                reader.Close();
                return flag;
            }
            catch (Exception ex) { throw; }
        }
        public bool CheckNegativeList(string PersonalCode)
        {
            try
            {
                bool flag = false;
                string command = string.Format("SELECT ID FROM [{0}] WHERE PersonalCode=@PersonalCode", "NegativeList");
                OleDbParameter[] parameters = new OleDbParameter[1];
                parameters[0] = new OleDbParameter("@PersonalCode", PersonalCode);
                OleDbDataReader reader = OleDbHelper.ExecuteReader(this.ConnectionString, CommandType.Text, command, parameters);
                if (reader.HasRows)
                {
                    flag = true;
                }
                reader.Close();
                return flag;
            }
            catch (Exception ex) { throw; }
        }
        public DataTable GetByBatchFileID(string batchFileID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                Candidate can = new Candidate() { BatchFileID = batchFileID };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                {
                    {"BatchFileID", "BatchFileID"}
                };
                string command = CommandHelper.BuildSelectCommand(can, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"CVCode", "CVCode"},
                {"Channel", "Channel"},
                {"Team", "Team"},
                {"FullName_E", "FullName_E"},
                {"FullName_V", "FullName_V"},
                {"CellPhone", "CellPhone"},
                {"DateOfBirth", "DateOfBirth"},
                {"College", "College"},
                {"Qualification", "Qualification"},
                {"IDNumber", "IDNumber"},
                {"IssuedDate", "IssuedDate"},
                {"IssuedPlace", "IssuedPlace"},
                {"PermanentAddress", "PermanentAddress"},
                {"Interviewer", "Interviewer"},
                {"InterviewDate", "InterviewDate"},
                {"TrainingCallStatus", "TrainingCallStatus"},
                {"TrainingCallStatusRemarks", "TrainingCallStatusRemarks"},
                {"CICSanctionDeliveryDate", "CICSanctionDeliveryDate"},
                {"CICSanctionDelivererName", "CICSanctionDelivererName"},
                {"CICResult", "CICResult"},
                {"SanctionResult", "SanctionResult"},
                {"PECCheckResult", "PECCheckResult"},
                {"PECRemarks", "PECRemarks"},
                {"TrainingBatch", "TrainingBatch"},
                {"TrainingDate", "TrainingDate"},
                {"TrainerName", "TrainerName"},
                {"TrainingAttendance", "TrainingAttendance"},
                {"TrainingAttendanceRemarks", "TrainingAttendanceRemarks"},
                {"ReTest", "ReTest"},
                {"ReTestRequirement", "ReTestRequirement"},
                {"TrainingResult", "TrainingResult"},
                {"OBEligible", "OBEligible"},
                {"DocumentCompletion", "DocumentCompletion"},
                {"DocumentIncompletionRemarks", "DocumentIncompletionRemarks"},
                {"PCReceiptSubmissionDate", "PCReceiptSubmissionDate"},
                {"PCReportSubmissionDate", "PCReportSubmissionDate"},
                {"HiringManagerName", "HiringManagerName"},
                {"HiringManagerAcknowledgeDate", "HiringManagerAcknowledgeDate"},
                {"DelegateApproverName", "DelegateApproverName"},
                {"ApprovingDate", "ApprovingDate"},
                {"OnBoarding", "OnBoarding"},
                {"OnBoardingRemarks", "OnBoardingRemarks"},
                {"CICSanctionRequestDate", "CICSanctionRequestDate"},
                {"TrainingResultCC", "TrainingResultCC"},
                {"CheckChannel", "CheckChannel"},
                {"BatchFileID", "BatchFileID"},
                {"Role", "Role"},
                {"Level", "Level"},
                {"Category", "Category"},
                {"ProductCategory", "ProductCategory"},
                {"Product", "Product"},
                {"TeamLeaderCode", "TeamLeaderCode"},
                {"JoiningDate", "JoiningDate"},
                {"PromotionTransferDate", "PromotionTransferDate"},
                {"Status", "Status"},
                {"FingerID", "FingerID"},
                {"PersonalTaxCode", "PersonalTaxCode"},
                {"ContractType", "ContractType"},
                {"ContractTerm", "ContractTerm"},
                {"ContractorName", "ContractorName"},
                {"ContractSignDate", "ContractSignDate"},
                {"ContractExpiryDate", "ContractExpiryDate"},
                {"BankAccount", "BankAccount"},
                {"BankBranchAddress", "BankBranchAddress"},
                {"BankName", "BankName"},
                {"FillingRefCode", "FillingRefCode"},
                {"ProfressiveTarget", "ProfressiveTarget"}
            };

            return dictionary;
        }

        public Candidate GetById(string sCVCode)
        {
            try
            {

                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                Candidate candidate = new Candidate { CVCode = sCVCode };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"CVCode", "CVCode"}
                    };
                string command = CommandHelper.BuildSelectCommand(candidate, dict, conditionDict, out parameters);
                OleDbDataReader reader = OleDbHelper.ExecuteReader(this.ConnectionString, CommandType.Text, command, parameters);
                List<Candidate> candidates = new List<Candidate>();
                while (reader.Read())
                {
                    candidates.Add(PopulateObjectHelper.PopulateCandidateObject(reader));
                }
                reader.Close();
                return (candidates.Count > 0 ? candidates[0] : null);
            }
            catch (Exception ex) { throw; }
        }
    }
}
